• Pas de documents autorisés
  • Pas de téléphone portable
Rappel de notation

Dans le formalisme Entité-Association, nous utilisons les pattes de corbeau (crowfoot) pour décrire les contraintes de cardinalités.

erDiagram

A }|..|{ B : foo

L’entité A est reliée à l’entité B par l’association foo. Une instance de A peut apparaître de \(1\) à \(n\) fois parmi les instances de l’association foo, de même une instance de B peut apparaître de \(1\) à \(n\) fois parmi les instances de l’association foo.

erDiagram

A }|..o| B : bar

L’entité A est reliée à l’entité B par l’association bar. Une instance de A peut apparaître de \(0\) à \(1\) fois parmi les instances de l’association bar, une instance de B peut apparaître de \(1\) à \(n\) fois parmi les instances de l’association bar.

Notez que la contrainte portant sur le nombre de participations des instances d’une entité à une association apparaît à l’opposé de l’entité.

Un système d’information pour l’administration

Un pays (par exemple France) est organisé en régions (par exemple Bretagne, …). Les régions sont identifiées par leur nom. Ces régions sont-elles même divisées en départements (par exemple Finistère). Les départements sont identifiés par de numéros (par exemple 29 pour le Finistère). Les départments possèdent aussi un nom qui les distingue.

Dans chaque département, il y a des communes qui portent des noms, éventuellement précédés d’un article (par exemple Le Mans, Le Bourget, …). Pour chaque département, les communes sont numérotées à l’aide de leur rang dans l’ordre alphabétique construit sur les noms privés de l’éventuel article (dans la Sarthe, 72, Le Mans est au rang 181 entre Mamers et Mansigné).

Pour chaque commune et chaque année, on connaît le montant total perçu au titre de la taxe foncière.

Chaque année, on procède à un recensement dans chaque commune. On en connaît la population.

Chaque région a une préfecture de région, et chaque département a une préfecture de département.

On a cherché à modéliser cette description en formalisme Entité-Association comme suit.

%%{init: { "sequence": { "wrap": true} } }%%
erDiagram 
    DEPARTEMENT }|..|{ REGION : contient
    DEPARTEMENT {
        string nom
        int departement 
    }
    REGION ||..o| VILLE : prefecture
    REGION {
        string nom   
    }
    REGION ||..|{ VILLE : contient
    DEPARTEMENT |o..|| VILLE : prefecture
    VILLE {
        string nom
        string article
        int numero       
    }
    
    RECENSEMENT ||..|{ VILLE : concerne
    RECENSEMENT {
      int annee 
      int population
    }
    TAXE ||..|{ VILLE : rapporte
    TAXE {
      int annee
      int valeur
    }

Question 1
  1. Un certain nombre d’erreurs se sont glissées dans ce schéma Entité-Association. Corrigez-le.
  2. Proposez un identifiant pour chaque entité.
  3. Désignez les entités faibles.

Soit le schéma \(\mathcal{A}=\left\{\texttt{A},\texttt{B},\texttt{C},\texttt{D},\texttt{E},\texttt{F}\right\}\) et l’ensemble \(\Sigma\) de DF

   A,B → C
     B → F  
   C,E → A  
     F → E
Question 2

Calculer la clôture/fermeture \(\{\texttt{A},\texttt{B}\} = [\{\texttt{A},\texttt{B}\}]_{\Sigma}^+\) de \(\{\texttt{A},\texttt{B}\}\) en utilisant l’ensemble de DF \(\Sigma\).

Question 3

Quelles sont les clefs du schéma \(\mathcal{A}\) en utilisant l’ensemble de DF \(\Sigma\) ?

On travaillera par la suite sur le schéma relationnel villes et les tables définies par le code SQL suivant.

CREATE SCHEMA villes;

CREATE TABLE villes.region (
  nom varchar NOT NULL PRIMARY KEY,
  prefecture_num int4 NULL,
  prefecture_dep int4 NULL
);

CREATE TABLE villes.departement (
  nom varchar NULL UNIQUE,
  departement int4 NOT NULL PRIMARY KEY,
  region varchar NULL REFERENCES villes.region(nom)
    ON DELETE SET NULL ON UPDATE CASCADE,
  numero int4 NULL
);

CREATE TABLE villes.ville (
  nom varchar NULL,
  article varchar NULL,
  departement int4 NOT NULL REFERENCES villes.departement
    ON DELETE RESTRICT ON UPDATE CASCADE,
  numero int4 NOT NULL,
  CONSTRAINT ville_pkey PRIMARY KEY (departement, numero)
);

ALTER TABLE villes.region ADD 
  CONSTRAINT region_prefecture_fk FOREIGN KEY (prefecture_dep,prefecture_num) 
  REFERENCES villes.ville(departement,numero) 
  ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE villes.departement ADD 
  CONSTRAINT departement_prefecture_fk FOREIGN KEY (departement,numero) 
  REFERENCES villes.ville(departement,numero) 
  ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE villes.recensement (
  annee year NOT NULL,
  departement int4 NOT NULL,
  numero int4 NOT NULL,
  population int4 NULL,
  CONSTRAINT recensement_pk PRIMARY KEY (annee, departement, numero),
  CONSTRAINT recensement_fk FOREIGN KEY (departement,numero) 
    REFERENCES villes.ville(departement,numero) 
    ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE villes.taxe (
  annee year NOT NULL,
  departement int4 NOT NULL,
  numero int4 NOT NULL,
  valeur int4 NULL,
  CONSTRAINT taxe_pk PRIMARY KEY (annee, departement, numero),
  CONSTRAINT taxe_fk  FOREIGN KEY (departement,numero) 
    REFERENCES villes.ville(departement,numero) 
    ON DELETE RESTRICT ON UPDATE CASCADE
);

Toutes les questions qui suivent se rapportent au schéma villes.

Pour chaque question, proposer une requête écrite en algèbre relationnelle ou en SQL.

Question 4

Lister par région le nombre de communes.

Question 5

Quel département contient le plus de communes ?

Question 6

Quel département a connu la plus forte croissance démographique relative entre 2010 et 2020 ?

Question 7

Pour chaque région, quelle est la proportion de la population qui vit dans des préfectures départementales en 2020 ?